package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TInsuranceCheckModel;
import com.iris.live.services.data.models.用户Model;
import com.iris.live.services.data.repositories.InsuranceCheckRepositorySql;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.DataCheckService;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: wangzhenyan
 * 2017/5/22 15:33
 */
@Repository
public class InsuranceCheckRepositorySqlImpl extends BaseRepository implements InsuranceCheckRepositorySql {

    @Override
    public List<TInsuranceCheckModel> findInsuranceCheckByImportBatchAndUser(String importBatch, 用户Model user) {
        String sql = "SELECT a.* FROM t_insurance_check a \n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit" ;
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",user.get许可());
        return this.getList(sql,params);
    }

    @Override
    public PageData<Map<String, Object>> getContrastResultOfInsurance(RequestBase req, String importBatch, String permit) {
        String sql = "SELECT\n" +
                "\ta.id,\n" +
                "\ta.vin,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_insured_cust_name END AS importInsuredCustName,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_policy_num END AS importTciPolicyNum,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_policy_num END AS importVciPolicyNum,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_invoice_date END AS importTciInvoiceDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_invoice_date END AS importVciInvoiceDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_effect_date END AS importTciEffectDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_expiration_date END AS importTciExpirationDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_effect_date END AS importVciEffectDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_expiration_date END AS importVciExpirationDate,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_company END AS importTciCompany,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_company END AS importVciCompany,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_price END AS importTciPrice,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_price END AS importVciPrice,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_TCI_fee END AS importTciFee,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.import_VCI_fee END AS importVciFee,\n" +
                "CASE WHEN a.deduction_insured_cust_name = 0 THEN '1' ELSE '0' END AS deductionInsuredCustName,\n" +
                "CASE WHEN a.deduction_TCI_policy_num = 0 THEN '1' ELSE '0' END AS deductionTciPolicyNum,\n" +
                "CASE WHEN a.deduction_VCI_policy_num = 0 THEN '1' ELSE '0' END AS deductionVciPolicyNum,\n" +
                "CASE WHEN a.deduction_TCI_invoice_date = 0 THEN '1' ELSE '0' END AS deductionTciInvoiceDate,\n" +
                "CASE WHEN a.deduction_VCI_invoice_date = 0 THEN '1' ELSE '0' END AS deductionVciInvoiceDate,\n" +
                "CASE WHEN a.deduction_TCI_effect_date = 0 THEN '1' ELSE '0' END AS deductionTciEffectDate,\n" +
                "CASE WHEN a.deduction_TCI_expiration_date = 0 THEN '1' ELSE '0' END AS deductionTciExpirationDate,\n" +
                "CASE WHEN a.deduction_VCI_effect_date = 0 THEN '1' ELSE '0' END AS deductionVciEffectDate,\n" +
                "CASE WHEN a.deduction_VCI_expiration_date = 0 THEN '1' ELSE '0' END AS deductionVciExpirationDate,\n" +
                "CASE WHEN a.deduction_TCI_company = 0 THEN '1' ELSE '0' END AS deductionTciCompany,\n" +
                "CASE WHEN a.deduction_VCI_company = 0 THEN '1' ELSE '0' END AS deductionVciCompany,\n" +
                "CASE WHEN a.deduction_TCI_price = 0 THEN '1' ELSE '0' END AS deductionTciPrice,\n" +
                "CASE WHEN a.deduction_VCI_price = 0 THEN '1' ELSE '0' END AS deductionVciPrice,\n" +
                "CASE WHEN a.deduction_TCI_fee = 0 THEN '1' ELSE '0' END AS deductionTciFee,\n" +
                "CASE WHEN a.deduction_VCI_fee = 0 THEN '1' ELSE '0' END AS deductionVciFee,\n" +
                "\ta.compare_result AS compareResult,\n" +
                "\ta.total_score AS totalScore\n" +
                "FROM t_insurance_check a\n" +
                "LEFT JOIN 用户 b ON a.import_user = b.`用户名`\n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit\n" +
                "AND a.compare_result <> '04'" ;
//                "AND b.角色 LIKE :角色";
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",permit);
//        params.put("角色","%保险%");  // 保险顾问和金融保险经理
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }

    @Override
    public PageData getRankingOfInsurance(DataCheckService.RankingRequest req) {
        String sql = "SELECT cast(@rowNum \\:= @rowNum + 1 AS DECIMAL(3,0))AS ranking, " +
                "  f.permit, " +
                "  f.`经销商名`  AS distributor, " +
                "  f.`大区`, " +
                "  IFNULL(score,'0') AS score " +
                "FROM ( " +
                "  SELECT  " +
                "  @rowNum \\:= 0 AS ranking, " +
                "  a.permit, " +
                "  b.`经销商名`, " +
                "  b.`大区`, " +
                "  cast(avg(a.total_score) AS DECIMAL(5,2)) score  " +
                "  FROM  " +
                "    ( " +
                "    SELECT * FROM t_insurance_check WHERE cast(import_batch AS date) >= cast(:startBatch AS date) AND cast(import_batch as date) <= cast(:endBatch AS date)  " +
                "    ) a " +
                "LEFT JOIN `lsh经销商表` b ON a.permit = b.`许可_BMBS` " +
                "GROUP BY a.permit " +
                "HAVING b.`大区` = ( " +
                "    SELECT c.大区 from `lsh经销商表` c WHERE c.`许可_BMBS` = :permit)          " +
                "ORDER BY AVG(a.total_score) DESC  " +
                ") f";

        Map<String,Object> params = new HashMap<>();
        params.put("permit",req.permit); //许可
        params.put("startBatch",req.startBatch); // 开始批次
        params.put("endBatch",req.endBatch); //结束批次
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }
}
